"""
数据库配置和连接管理
"""

from sqlalchemy.ext.asyncio import create_async_engine, AsyncSession, async_sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import MetaData, text
from contextlib import asynccontextmanager
from typing import AsyncGenerator
import logging

from .config import settings
from .logging import get_logger

logger = get_logger(__name__)

# 数据库引擎
engine = None
async_session_factory = None

# 声明基类
Base = declarative_base()


async def init_db():
    """初始化数据库"""
    global engine, async_session_factory
    
    try:
        # 创建异步引擎
        if settings.DATABASE_URL.startswith('sqlite'):
            # SQLite 数据库配置
            engine = create_async_engine(
                settings.DATABASE_URL,
                echo=settings.DATABASE_ECHO,
                connect_args={"check_same_thread": False}
            )
        else:
            # 其他数据库配置（如 PostgreSQL, MySQL 等）
            engine = create_async_engine(
                settings.DATABASE_URL,
                echo=settings.DATABASE_ECHO,
                pool_pre_ping=True,
                pool_recycle=3600,
                pool_size=10,
                max_overflow=20
            )
        
        # 创建会话工厂
        async_session_factory = async_sessionmaker(
            engine,
            class_=AsyncSession,
            expire_on_commit=False
        )
        
        # 创建所有表
        await create_tables()
        
        logger.info("Database initialized successfully")
        return True
        
    except Exception as e:
        logger.error(f"Failed to initialize database: {e}")
        return False


async def create_tables():
    """创建所有表"""
    try:
        # 导入所有模型以确保它们被注册
        from ..models import audiobook, voice_profile, audio_stream, user, task
        
        # 直接创建表，避免手动检查关系
        # 这样可以解决 isinstance() 类型错误问题
        logger.info("Creating database tables...")
        
        # 创建表 - 简化版本，避免额外的类型检查
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.create_all)
        
        logger.info("Database tables created successfully")
        
    except Exception as e:
        logger.error(f"Failed to create database tables: {e}")
        raise


async def drop_tables():
    """删除所有表"""
    try:
        async with engine.begin() as conn:
            await conn.run_sync(Base.metadata.drop_all)
        
        logger.info("Database tables dropped successfully")
        
    except Exception as e:
        logger.error(f"Failed to drop database tables: {e}")
        raise


@asynccontextmanager
async def get_db_session() -> AsyncGenerator[AsyncSession, None]:
    """获取数据库会话"""
    if async_session_factory is None:
        raise RuntimeError("Database not initialized")
    
    session = async_session_factory()
    try:
        yield session
        await session.commit()
    except Exception as e:
        await session.rollback()
        raise
    finally:
        await session.close()


async def get_db() -> AsyncSession:
    """获取数据库会话（依赖注入用）"""
    async with get_db_session() as session:
        yield session


class DatabaseManager:
    """数据库管理器"""
    
    def __init__(self):
        self.engine = None
        self.session_factory = None
    
    async def initialize(self, database_url: str = None):
        """初始化数据库"""
        db_url = database_url or settings.DATABASE_URL
        
        if db_url.startswith('sqlite'):
            # SQLite 数据库配置
            self.engine = create_async_engine(
                db_url,
                echo=settings.DATABASE_ECHO,
                connect_args={"check_same_thread": False}
            )
        else:
            # 其他数据库配置（如 PostgreSQL, MySQL 等）
            self.engine = create_async_engine(
                db_url,
                echo=settings.DATABASE_ECHO,
                pool_pre_ping=True,
                pool_recycle=3600,
                pool_size=10,
                max_overflow=20
            )
        
        self.session_factory = async_sessionmaker(
            self.engine,
            class_=AsyncSession,
            expire_on_commit=False
        )
        
        await self.create_tables()
    
    async def create_tables(self):
        """创建表"""
        try:
            # 导入所有模型以确保它们被注册
            from ..models import audiobook, voice_profile, audio_stream, user, task
            from sqlalchemy import Table, Column, Integer, String, ForeignKey
            from sqlalchemy.orm import relationship
            
            # 确保所有模型类都已经被导入和处理
            for model in [audiobook, voice_profile, audio_stream, user, task]:
                for name, cls in model.__dict__.items():
                    if isinstance(cls, type) and hasattr(cls, '__tablename__'):
                        # 记录模型类已被加载
                        logger.debug(f"Model {cls.__name__} loaded")
            
            # 创建表
            async with self.engine.begin() as conn:
                await conn.run_sync(Base.metadata.create_all)
            
            logger.info("Database tables created successfully by DatabaseManager")
            
        except Exception as e:
            logger.error(f"DatabaseManager failed to create tables: {e}")
            raise
    
    async def drop_tables(self):
        """删除表"""
        async with self.engine.begin() as conn:
            await conn.run_sync(Base.metadata.drop_all)
    
    async def close(self):
        """关闭数据库连接"""
        if self.engine:
            await self.engine.dispose()
    
    @asynccontextmanager
    async def get_session(self) -> AsyncGenerator[AsyncSession, None]:
        """获取会话"""
        if self.session_factory is None:
            raise RuntimeError("Database not initialized")
        
        session = self.session_factory()
        try:
            yield session
            await session.commit()
        except Exception as e:
            await session.rollback()
            raise
        finally:
            await session.close()


# 全局数据库管理器
db_manager = DatabaseManager()


# 数据库健康检查
async def check_database_health() -> dict:
    """检查数据库健康状态"""
    try:
        async with get_db_session() as session:
            # 执行简单查询测试连接
            await session.execute(text("SELECT 1"))
            
            return {
                "status": "healthy",
                "message": "Database connection is working"
            }
    except Exception as e:
        return {
            "status": "unhealthy",
            "message": f"Database connection failed: {str(e)}"
        }


# 数据库统计信息
async def get_database_stats() -> dict:
    """获取数据库统计信息"""
    try:
        async with get_db_session() as session:
            stats = {}
            
            # 这里可以添加各种统计查询
            # 例如：表记录数、数据库大小等
            
            return stats
    except Exception as e:
        logger.error(f"Failed to get database stats: {e}")
        return {}


# 事务装饰器
def transactional(func):
    """事务装饰器"""
    async def wrapper(*args, **kwargs):
        async with get_db_session() as session:
            return await func(session, *args, **kwargs)
    return wrapper


# 数据库迁移支持
class DatabaseMigration:
    """数据库迁移管理"""
    
    def __init__(self):
        self.migrations = {}
    
    def add_migration(self, version: str, migration_func):
        """添加迁移"""
        self.migrations[version] = migration_func
    
    async def run_migration(self, version: str):
        """运行指定版本的迁移"""
        if version not in self.migrations:
            raise ValueError(f"Migration {version} not found")
        
        migration_func = self.migrations[version]
        await migration_func()
    
    async def run_all_migrations(self):
        """运行所有迁移"""
        for version in sorted(self.migrations.keys()):
            await self.run_migration(version)


# 全局迁移管理器
migration_manager = DatabaseMigration()
